﻿using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Shippers : System.Web.UI.Page 
{
    SqlConnection con;
    SqlDataAdapter da;
    DataTable dt;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (this.IsPostBack == false)
        {
            FillShippers();
            ViewState["isSearch"] = false;
            ViewState["Sort"] = "ASC";
            ViewState["gv"] = gvShippers.DataSource;
        }
    }
    void FillShippers()
    {
        try
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            da = new SqlDataAdapter("getShippers", con);
            dt = new DataTable("Sales.Shippers");
            da.Fill(dt);
            gvShippers.AutoGenerateSelectButton = true;
            gvShippers.AllowPaging = true;
            gvShippers.PageSize = 5;
            txtSearch.Text = "";
            
            gvShippers.DataSource = dt;
            gvShippers.DataBind();
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Load Data Fail !" + "')</script>");
        }
        
    }
    void addShipper()
    {
        con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString; ;
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "InsertShipper";

        SqlParameter param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
        param.Value = txtCompanyName.Text;
        cmd.Parameters.Add(param);

        param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
        param.Value = txtPhone.Text;
        cmd.Parameters.Add(param);

        cmd.ExecuteNonQuery();
        con.Close();
    }
    void updateShipper()
    {
        con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString; ;
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "UpdateShipper";

        SqlParameter param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
        param.Value = txtCompanyName.Text;
        cmd.Parameters.Add(param);

        param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
        param.Value = txtPhone.Text;
        cmd.Parameters.Add(param);

        param = new SqlParameter("@shipperid", SqlDbType.Int);
        param.Value = txtShipperid.Text;
        cmd.Parameters.Add(param);

        cmd.ExecuteNonQuery();
        con.Close();
    }
    void deleteShipper()
    {
        con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString; ;
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "DeleteShipper";

        SqlParameter param = new SqlParameter("@shipperid", SqlDbType.Int);
        param.Value = txtShipperid.Text;
        cmd.Parameters.Add(param);

        cmd.ExecuteNonQuery();
        con.Close();
    }
    void Search()
    {
        con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        da = new SqlDataAdapter("getShippers", con);
        dt = new DataTable("Sales.Shippers");
        da.Fill(dt);
        gvShippers.AutoGenerateSelectButton = true;
        gvShippers.AllowPaging = true;
        gvShippers.PageSize = 5;
        DataView dv = new DataView(dt);
        if (ddlSearch.SelectedIndex == 0)
        {
            dv.RowFilter = string.Format("shipperid = '{0}'", txtSearch.Text);
        }
        else
            if (ddlSearch.SelectedIndex == 1)
            {
                dv.RowFilter = string.Format("companyname like '%{0}%'", txtSearch.Text);
            }
        
        gvShippers.DataSource = dv;
        gvShippers.DataBind();        
    }

    void ClearShipper()
    {
        txtShipperid.Text = "";
        txtCompanyName.Text = "";
        txtPhone.Text = "";
        txtSearch.Text = "";
    }
    protected void gvShippers_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            gvShippers.PageIndex = e.NewPageIndex;
            gvShippers.DataBind();
            if (!(bool)ViewState["isSearch"])
                FillShippers();
            else
                Search();
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Error !" + "')</script>");
        }
    }
    protected void gvShippers_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            GridViewRow r = gvShippers.SelectedRow;
            txtShipperid.Text = r.Cells[1].Text;
            txtCompanyName.Text = Server.HtmlDecode(r.Cells[2].Text);
            txtPhone.Text = r.Cells[3].Text;
            btnAdd.Enabled = false;
            btnUpdate.Enabled = true;
            btnDelete.Enabled = true;
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Error !" + "')</script>");
        }
    }
    protected void btnNew_Click(object sender, EventArgs e)
    {
        try
        {
            FillShippers();
            btnAdd.Enabled = true;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            ClearShipper();
        }
        catch (Exception)
        {
            
        }
    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        try
        {
            addShipper();
            FillShippers();
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Add Fail !" + "')</script>");
        }
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        try
        {
            updateShipper();
            FillShippers();
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Update Fail !" + "')</script>");
        }
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        try
        {
            deleteShipper();
            FillShippers();
            ClearShipper();
        }
        catch (Exception)
        {
        }
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        try
        {
            ViewState["isSearch"] = true;
            Search();
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Search Fail !" + "')</script>");
        }
    }
    protected void btnShowAll_Click(object sender, EventArgs e)
    {
        FillShippers();
        ClearShipper();
    }
    protected void gvShippers_Sorting(object sender, GridViewSortEventArgs e)
    {
        DataView dv = new DataView((DataTable)ViewState["gv"]);
        dv.Sort = e.SortExpression + " " + (string)ViewState["Sort"];
        if ((string)ViewState["Sort"] == "ASC")
        {
            ViewState["Sort"] = "DESC";
        }
        else
        {
            ViewState["Sort"] = "ASC";
        }

        dt = dv.ToTable();
        gvShippers.DataSource = dt;
        gvShippers.DataBind();
    }
}
